package Dao;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import entity.SalesRecord;
import entity.UserInfo;
import util.DBConnection;

public class UserInfoDao {
    public UserInfo findUserByUsernameAndPassword(String username, String password) throws SQLException {
        UserInfo userInfo = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            // 通过DBConnection类获取数据库连接
            conn = DBConnection.getConnection();
            // 编写SQL语句，查询用户信息，根据用户名和密码查找
            String sql = "SELECT * FROM userinfo WHERE username =? AND password =?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            pstmt.setString(2, password);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                userInfo = new UserInfo();
                userInfo.setUserID(rs.getInt("userID"));
                userInfo.setUsername(rs.getString("username"));
                userInfo.setPassword(rs.getString("password"));
                userInfo.setEmail(rs.getString("email"));
                userInfo.setRegisterDate(rs.getDate("registerDate"));
                userInfo.setGender(rs.getString("gender"));
                userInfo.setMemberLevel(rs.getString("memberLevel"));
                userInfo.setTotalConsumption(rs.getBigDecimal("totalConsumption"));
                userInfo.setRole(rs.getString("role"));
            }
        } finally {
//        	DBConnection.close(rs, pstmt, conn);
            rs.close();
            pstmt.close();
            conn.close();
        }
        return userInfo;
    }


    // 用于更新用户信息的方法，接收一个UserInfo对象作为参数，对象中包含了要更新的用户信息
    public int updateUserInfo(UserInfo userInfo) throws SQLException {
        int rowsAffected = 0;
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            // 通过DBConnection类获取数据库连接
            conn = DBConnection.getConnection();
            // 编写SQL语句，更新用户信息，根据用户ID来确定要更新的具体记录，这里假设userInfo对象中的userID属性已正确设置
            String sql = "UPDATE userinfo SET username =?, password =?, email =?, gender =? WHERE userID =?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userInfo.getUsername());
            pstmt.setString(2, userInfo.getPassword());
            pstmt.setString(3, userInfo.getEmail());
            pstmt.setString(4, userInfo.getGender());
            pstmt.setInt(5, userInfo.getUserID());


            // 执行更新操作，返回受影响的行数，即更新了几条记录
            rowsAffected = pstmt.executeUpdate();
        } finally {
            // 调用DBConnection类中的方法关闭相关资源，确保资源正确释放
//                DBConnection.close(null,pstmt, conn);
            pstmt.close();
            conn.close();
        }

        return rowsAffected;
    }

    public static UserInfo findUserByUserID(int userid) throws SQLException {
        UserInfo userInfo = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            // 通过DBConnection类获取数据库连接
            conn = DBConnection.getConnection();
            // 编写SQL语句，查询用户信息，根据用户名和密码查找
            String sql = "SELECT * FROM userinfo WHERE userid =?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userid);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                userInfo = new UserInfo();
                userInfo.setUserID(rs.getInt("userID"));
                userInfo.setUsername(rs.getString("username"));
                userInfo.setPassword(rs.getString("password"));
                userInfo.setEmail(rs.getString("email"));
                userInfo.setRegisterDate(rs.getDate("registerDate"));
                userInfo.setGender(rs.getString("gender"));
                userInfo.setMemberLevel(rs.getString("memberLevel"));
                userInfo.setTotalConsumption(rs.getBigDecimal("totalConsumption"));
                userInfo.setRole(rs.getString("role"));
            }
        } finally {
//            	DBConnection.close(rs, pstmt, conn);
            rs.close();
            pstmt.close();
            conn.close();
        }
        return userInfo;
    }



    //查询所有用户姓名
    public ArrayList<String> findUserName() throws SQLException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<String> usernames = new ArrayList<>();
        try {
            // 通过DBConnection类获取数据库连接
            conn = DBConnection.getConnection();
            // 编写SQL语句，查询用户信息，根据用户名和密码查找
            String sql = "SELECT * FROM userinfo";
            pstmt = conn.prepareStatement(sql);

            rs = pstmt.executeQuery();
            while (rs.next()) {
                usernames.add(rs.getString("username"));
            }
        } finally {
//            	DBConnection.close(rs, pstmt, conn);
            rs.close();
            pstmt.close();
            conn.close();
        }
        return usernames;
    }



    public int registerNewUser(String p_Username, String p_Password, String p_Email, String p_Gender) throws SQLException {
        // 调用存储过程的SQL语句
//        	System.out.println("Gender: " + p_Gender);
//        	return 0;
        Connection conn = null;
        ArrayList<String> usernames =  new ArrayList<>();
        conn = DBConnection.getConnection();
        String sql = "{call RegisterNewUser(?,?,?,?, '用户')}";


        CallableStatement callableStatement = conn.prepareCall(sql) ;

        // 设置存储过程的输入参数
        callableStatement.setString(1, p_Username);
        callableStatement.setString(2, p_Password);
        callableStatement.setString(3, p_Email);
        callableStatement.setString(4, p_Gender);


        usernames = findUserName();
        int flag=0;
        for(int i =0 ;i<usernames.size();i++) {
            if(p_Username.equals(usernames.get(i))) {
                flag=1;
            }
        }

        if(flag==0) {
            // 执行存储过程
            callableStatement.execute();
            return 1;
        }else {
            return 0;
        }

    }

    public List<SalesRecord> getSalesRecordsByUserId(int userId,String memberLevel) throws SQLException {
        List<SalesRecord> salesRecords = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBConnection.getConnection();
            String sql = "SELECT sr.recordID, s.SpotName, sr.TicketQuantity, sr.ConsumptionAmount, sr.SaleTime, u.MemberLevel " +
                    "FROM SalesRecord sr " +
                    "JOIN ScenicSpot s ON sr.SpotID = s.SpotID " +
                    "JOIN UserInfo u ON sr.UserID = u.UserID " +
                    "WHERE sr.UserID =?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                SalesRecord record = new SalesRecord();
                record.setSpotName(rs.getString("SpotName"));
                record.setTicketQuantity(rs.getInt("TicketQuantity"));
                record.setConsumptionAmount(rs.getBigDecimal("ConsumptionAmount"));
                record.setSaleTime(rs.getDate("SaleTime"));
                record.setMemberLevel(rs.getString("MemberLevel"));
                salesRecords.add(record);
            }
        } finally {
//                DBConnection.close(rs, pstmt, conn);
            rs.close();
            pstmt.close();
            conn.close();
        }

        return salesRecords;
    }


    public static BigDecimal getUserDiscountRate(int userID) throws SQLException {
        String sql = "SELECT MemberLevel FROM UserInfo WHERE UserID =?";
        try (Connection conn = DBConnection.getConnection();
             PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
            preparedStatement.setInt(1, userID);
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                if (resultSet.next()) {
                    String memberLevel = resultSet.getString("MemberLevel");
                    switch (memberLevel) {
                        case "金卡会员":
                            return BigDecimal.valueOf(0.7);
                        case "银卡会员":
                            return BigDecimal.valueOf(0.8);
                        default:
                            return BigDecimal.ONE;
                    }
                }
                return BigDecimal.ONE; // 如果未查询到会员等级，默认返回无折扣（1）
            }
        }
    }









}

